IF OBJECT_ID('ssf.ddl_drop_view') IS NOT NULL
	DROP PROCEDURE ssf.ddl_drop_view
GO

CREATE PROCEDURE ssf.ddl_drop_view
	@view_name NVARCHAR(512)
AS
/*

*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@sql_error INT
			, @sql_error2 INT
			, @row_count BIGINT
			, @sql VARCHAR(MAX)
			, @nsql NVARCHAR(MAX)
			, @nsql2 NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @two_quotes CHAR(2) = CHAR(39) + CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@db_name NVARCHAR(128) = ISNULL(PARSENAME(@view_name, 3), DB_NAME())
			, @schema_name NVARCHAR(128) = ISNULL(PARSENAME(@view_name, 2), SCHEMA_NAME())
			, @view_local_name NVARCHAR(128) = ISNULL(PARSENAME(@view_name, 1), '')
			, @exists_fl BIT = NULL
-- ============

-- ============
/*<code_comment><![CDATA[test view existence]]></code_comment>*/
EXECUTE @sql_error = ssf.ddl_exists_view
	@view_name = @view_name
	-- NVARCHAR(512)
	, @exists_fl = @exists_fl OUTPUT
	-- BIT OUTPUT

IF ISNULL(@sql_error, 1) <> 0
OR @exists_fl IS NULL
BEGIN
	RAISERROR('Failed to test view existence: <%s>.', 16, 1, @view_name)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[return unless the view exists]]></code_comment>*/
IF @exists_fl = 0
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[drop the view]]></code_comment>*/
SET @nsql = 'DROP VIEW ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@view_local_name)
SET @nsql2 = 'EXECUTE @sql_error = ' + QUOTENAME(@db_name) + '..sp_executesql @nsql'

PRINT @nsql
PRINT @crlf
PRINT @nsql2

EXECUTE @sql_error2 = sp_executesql
	@nsql2
	, N'@nsql NVARCHAR(MAX), @sql_error INT OUTPUT'
	, @nsql
	, @sql_error OUTPUT

IF ISNULL(@sql_error, 1) <> 0
OR ISNULL(@sql_error2, 1) <> 0
BEGIN
	RAISERROR('Dynamic sql failed: %s.', 16, 1, @nsql)
	RETURN @sql_error
END
-- ============

RETURN @@ERROR
GO
